﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Data.SqlClient;
using Microsoft.Data.Sqlite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Threading;
using System.IO;
using Microsoft.Office.Interop.Word;
using DataTable = System.Data.DataTable;
using Font = System.Drawing.Font;

namespace website_monitor
{
    public partial class Report : Form
    {
        public Report()
        {
            InitializeComponent();
            Control.CheckForIllegalCrossThreadCalls = false;
        }

        private void Report_Load(object sender, EventArgs e)
        {
            dateTimePicker1.Value = DateTime.Today.AddDays(-7d);
            dateTimePicker2.Value = DateTime.Today.AddDays(+1d);
            //getDate();
            ////绑定时间
            //StartProductTime.Value = DateTime.Today.AddDAys(-7d);
            //StopProdectTime.Value = DateTime.Today.AddDays(+1d);
            ////查询数据的方法 
            //getData();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime dts = Convert.ToDateTime(dateTimePicker1.Text);
                DateTime dte = Convert.ToDateTime(dateTimePicker2.Text);

                string b1 = Convert.ToString(DateTime.Now);
                string b2 = Convert.ToString(DateTime.Now);
                string path = "data source=Sec-montior.db";
                SQLiteConnection dataConn = new SQLiteConnection(path);
                SQLiteConnection sq = new SQLiteConnection(path);
                dataConn.Open();

                if (dataConn.State == ConnectionState.Open)
                {
                    DataTable dt = new DataTable();
                    //SELECT ID, title, url, time FROM sec_montior  SELECT ID, username, password FROM Sec_admin;
                    //SELECT username, password FROM sec_admin
                    string sql = "select * from sec_montior";
                    //string sql = "select * from sec_montior where time between " + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "'" +
                    //     "and'" + dateTimePicker2.Value.ToString("yyyy-MM-dd");
                    //string sql2 = string.Format(sql, b1.ToString(System.DateTime.Now), b2.ToString());
                    //SQLiteCommand command = new SQLiteCommand(sql, dataConn);
                    //command.ExecuteNonQuery();
                    SQLiteDataAdapter mAdapt = new SQLiteDataAdapter(sql,path);
                    mAdapt.Fill(dt);
                    dataGridView1.DataSource = dt;// 读取漏洞数据
                    toolStripStatusLabel2.Text = dataGridView1.RowCount.ToString();
                }
                else
                {
                    AppLog.WriteError("错误提示：" + "\r\n" + "时间：" + DateTime.Now + "\r\n", true);
                    MessageBox.Show("漏洞加载失败！");
                    return;
                }
            }
            catch (Exception ex)
            {
                AppLog.WriteError("错误提示：" + "\r\n" + "时间：" + DateTime.Now + "\r\n" + ex, true);
                Console.WriteLine("" + ex);
                MessageBox.Show("错误提示：" + "\r\n" + ex);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime dts = Convert.ToDateTime(dateTimePicker1.Text);
                DateTime dte = Convert.ToDateTime(dateTimePicker2.Text);

                string b1 = Convert.ToString(DateTime.Now);
                string b2 = Convert.ToString(DateTime.Now);
                string path = "data source=Sec-montior.db";
                SQLiteConnection dataConn = new SQLiteConnection(path);
                SQLiteConnection sq = new SQLiteConnection(path);
                dataConn.Open();

                if (dataConn.State == ConnectionState.Open)
                {
                    DataTable dt = new DataTable();
                    string sql = "select * from sec_montior where time between'" + dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'" +
                         "and'" + dateTimePicker2.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                    SQLiteDataAdapter mAdapt = new SQLiteDataAdapter(sql, path);
                    mAdapt.Fill(dt);
                    dataGridView1.DataSource = dt;
                    //统计条数
                    toolStripStatusLabel2.Text = dataGridView1.RowCount.ToString();
                }
                else
                {
                    AppLog.WriteError("错误提示：" + "\r\n" + "时间：" + DateTime.Now + "\r\n", true);
                    MessageBox.Show("漏洞加载失败！");
                    return;
                }
            }
            catch (Exception ex)
            {
                AppLog.WriteError("错误提示：" + "\r\n" + "时间：" + DateTime.Now + "\r\n" + ex, true);
                Console.WriteLine("" + ex);
                MessageBox.Show("错误提示：" + "\r\n" + ex);
            }

        }
        //窗体加载事件

        private void getDate()
        {
            string path = "data source=Sec-montior.db";
            SQLiteConnection dataConn = new SQLiteConnection(path);
            dataConn.Open();

            if (dataConn.State == ConnectionState.Open)
            {
                DataTable dt = new DataTable();
                string sql = string.Format("SELECT ID, title, url, time FROM sec_montior", dateTimePicker1.Value.ToString("yyyy-MM-dd"),dateTimePicker2.Value.ToString("yyyy-MM-dd"));
                SQLiteCommand command = new SQLiteCommand(sql, dataConn);
                int s2 = command.ExecuteNonQuery();
                toolStripStatusLabel2.Text = Convert.ToString(command.ToString());
                SQLiteDataAdapter mAdapt = new SQLiteDataAdapter(command);
                mAdapt.Fill(dt);
                dataGridView1.DataSource = dt;
            }

        }
        //public Thread tb;
        private void button3_Click(object sender, EventArgs e)
        {
            if(comboBox1.Text == string.Empty)
            {
                Console.WriteLine("请选择导出方式");
                MessageBox.Show("请选择导出方式");
                return;
            }
            if(comboBox1.Text == "word")
            {
                //如果选择的是word 就执行word 导出方式
                try
                {
                    string wordTemplatePath = System.Windows.Forms.Application.StartupPath + @"\template\test.docx";//定义模板路径
                    if (File.Exists(wordTemplatePath))
                    {
                        System.Windows.Forms.FolderBrowserDialog dirDialog = new System.Windows.Forms.FolderBrowserDialog();
                        dirDialog.ShowDialog();
                        if (dirDialog.SelectedPath != string.Empty)
                        {
                            string newFileName = dirDialog.SelectedPath + @"\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".docx";//保存文件名称格式年月日

                            Dictionary<string, string> wordLableList = new Dictionary<string, string>();//直接新建导出位置
                            Console.WriteLine(DateTime.Now + "   ------   " + "正在导出报告" + "\r\n");                                                                            //加载数据库取数据
                            //判断如果没有在datagridview中选择或加载数据 不继续执行,前提提交必须是有数据的情况下进行，否则导出的数据将会是空数据
                            if(dataGridView1.Rows.Count == 0)
                            {
                                Console.WriteLine("请加载或查询数据后进行导出");
                                MessageBox.Show("请加载或查询数据后进行导出");
                                return;
                            }
                            string path = "data source=Sec-montior.db";
                            SQLiteConnection dataConn = new SQLiteConnection(path);
                            dataConn.Open();
                            //dataConn.EnableExtensions(true);
                            //dataConn.LoadExtension("System.Data.SQLite.dll", "sqlite3_fts5_init");
                            if (dataConn.State == ConnectionState.Open)
                            {
                                DataSet ds = new DataSet();
                                string sql = "SELECT username, repname, addtime FROM Sec_reportset";
                                SQLiteCommand command = new SQLiteCommand(sql, dataConn);
                                command.ExecuteNonQuery();
                                SQLiteDataAdapter mAdapt = new SQLiteDataAdapter(command);
                                mAdapt.Fill(ds);
                                //dataGridView1.DataSource = ds.Tables[0];
                                var ss = mAdapt.ToString();
                                //判断是否进行执行 如果执行之后就进行打印
                                if (ss.Length != 1)
                                {
                                    //先把数据定义出来
                                    string s1 = ds.Tables[0].Rows[0]["username"].ToString();
                                    string s2 = ds.Tables[0].Rows[0]["repname"].ToString();
                                    string s3 = ds.Tables[0].Rows[0]["addtime"].ToString();
                                    //获取定义的数据
                                    Console.WriteLine(DateTime.Now + "   ------   " + "正在获取报告数据" + "\r\n");
                                    wordLableList.Add("单位名称", s1);
                                    wordLableList.Add("报告单位", s2);
                                    wordLableList.Add("创建时间", s3);
                                    wordLableList.Add("可用性数", dataGridView1.Rows.Count.ToString());//统计可用性数，前提条件是必须的加载数据，否则不能导出
                                    //执行datagridview的数据导出
                                    //尝试使用读取数据的方式先把数据读取出来
                                    //string path1 = "data source=Sec-montior.db";
                                    //SQLiteConnection dataConn1 = new SQLiteConnection(path1);
                                    //dataConn1.Open();
                                    //DataSet ds1 = new DataSet();
                                    //string sql1 = "SELECT ID, title, url, time FROM sec_montior";
                                    //SQLiteCommand command1 = new SQLiteCommand(sql1, dataConn1);
                                    //command1.ExecuteNonQuery();
                                    //SQLiteDataAdapter mAdapt1 = new SQLiteDataAdapter(command1);
                                    //mAdapt1.Fill(ds1);
                                    //string s4 = ds1.Tables[0].Rows[0]["ID"].ToString();
                                    //string s5 = ds1.Tables[0].Rows[0]["title"].ToString();
                                    //string s6 = ds1.Tables[0].Rows[0]["url"].ToString();
                                    //int i = dataGridView1.Rows.Count;//表示获取所有
                                    //wordLableList.Add("username", Convert.ToString(this.dataGridView1.Rows[0].Cells[1].Value.ToString()));
                                    object boj = dataGridView1.CurrentCell.Value;
                                    wordLableList.Add("ID", this.dataGridView1.Columns[0].HeaderText.ToString());
                                    //wordLableList.Add("url", this.dataGridView1.Columns[1].HeaderText.ToString() + dataGridView1.Rows[1].Cells[2].Value.ToString());//能同时获取内容和标题 ，但是不进行换行
                                    wordLableList.Add("url", this.dataGridView1.Columns[1].HeaderText.ToString());
                                    wordLableList.Add("title", this.dataGridView1.Columns[2].HeaderText.ToString());
                                    wordLableList.Add("time", this.dataGridView1.Columns[3].HeaderText.ToString());
                                    /*wordLableList.Add("username", Convert.ToString(this.dataGridView1.Rows[0].Cells[0].Value));*///这种只能获取数据 不能获取标题
                                    Export(wordTemplatePath, newFileName, wordLableList);
                                    MessageBox.Show("导出成功!");
                                    Console.WriteLine(DateTime.Now + "   ------   " + "报告导出成功" + "\r\n");
                                    dataConn.Close();
                                }
                            }
                        }
                        else
                        {
                            MessageBox.Show("请选择导出位置");
                        }
                    }
                    else
                    {
                        MessageBox.Show("Word模板文件不存在!");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    Console.WriteLine("错误提示：" + ex.ToString());
                    return;
                }

            }
            else
            {
                //否则就执行excel导出方式
                Console.WriteLine("执行时间：" + DateTime.Now + "   --------   " + "正在设置保存位置，如果没有文件软件会自动创建文件");
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "Export Excel File";
                saveFileDialog.ShowDialog();
                if (saveFileDialog.FileName == "")
                    return;
                Stream myStream;
                myStream = saveFileDialog.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

                string str = "";
                try
                {
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (i > 0)
                        {
                            str += "\t";
                        }
                        str += dataGridView1.Columns[i].HeaderText;
                    }
                    sw.WriteLine(str);
                    for (int j = 0; j < dataGridView1.Rows.Count; j++)
                    {
                        string tempStr = "";
                        for (int k = 0; k < dataGridView1.Columns.Count; k++)
                        {
                            if (k > 0)
                            {
                                tempStr += "\t";
                            }
                            Console.WriteLine("执行时间：" + DateTime.Now + "   --------   " + "数据保存成功！");
                            tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
                        }
                        sw.WriteLine(tempStr);
                    }
                    sw.Close();
                    myStream.Close();
                }

                catch (Exception ex)
                {
                    Console.WriteLine("错误时间：" + DateTime.Now + "   --------   " + "\r\n" + ex);
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }
        private void 设置ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //报告设置
            ReportSet reportSet = new ReportSet();
            reportSet.StartPosition = FormStartPosition.CenterScreen;
            reportSet.ShowDialog();
        }
        public static void Export(string wordTemplatePath, string newFileName, Dictionary<string, string> wordLableList)
        {
            Microsoft.Office.Interop.Word.Application app = new Microsoft.Office.Interop.Word.Application();
            string TemplateFile = wordTemplatePath;
            File.Copy(TemplateFile, newFileName);
            _Document doc = new Document();
            object obj_NewFileName = newFileName;
            object obj_Visible = false;
            object obj_ReadOnly = false;
            object obj_missing = System.Reflection.Missing.Value;

            doc = app.Documents.Open(ref obj_NewFileName, ref obj_missing, ref obj_ReadOnly, ref obj_missing,
                ref obj_missing, ref obj_missing, ref obj_missing, ref obj_missing,
                ref obj_missing, ref obj_missing, ref obj_missing, ref obj_Visible,
                ref obj_missing, ref obj_missing, ref obj_missing,
                ref obj_missing);
            doc.Activate();
            if (wordLableList.Count > 0)
            {
                object what = WdGoToItem.wdGoToBookmark;
                foreach (var item in wordLableList)
                {
                    object lableName = item.Key;
                    if (doc.Bookmarks.Exists(item.Key))
                    {
                        doc.ActiveWindow.Selection.GoTo(ref what, ref obj_missing, ref obj_missing, ref lableName);//光标移动书签的位置
                        doc.ActiveWindow.Selection.TypeText(item.Value);//在书签处插入的内容 
                        doc.ActiveWindow.Selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;//设置插入内容的Alignment
                    }
                }
            }
            object obj_IsSave = true;
            doc.Close(ref obj_IsSave, ref obj_missing, ref obj_missing);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strCaption"></param>
        /// <param name="myDGV"></param>
        /// <param name="saveFileDialog"></param>
        /// <returns></returns>
        public int ExportExcel(string strCaption, DataGridView myDGV, SaveFileDialog saveFileDialog)
        {
            int result = 9999;

            //保存

            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            //saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel File";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                if (saveFileDialog.FileName == "")
                {
                    MessageBox.Show("请输入保存文件名！");
                    saveFileDialog.ShowDialog();
                }
                // 列索引，行索引，总列数，总行数
                int ColIndex = 0;
                int RowIndex = 0;
                int ColCount = myDGV.ColumnCount;
                int RowCount = myDGV.RowCount;

                if (myDGV.RowCount == 0)
                {
                    result = 1;
                }

                // 创建Excel对象
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                //Microsoft.Office.Interop.Excel.
                if (xlApp == null)
                {
                    result = 2;
                }
                try
                {
                    // 创建Excel工作薄
                    Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                    Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
                    // 设置标题
                    Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同
                    range.MergeCells = true;
                    xlApp.ActiveCell.FormulaR1C1 = strCaption;
                    xlApp.ActiveCell.Font.Size = 20;
                    xlApp.ActiveCell.Font.Bold = true;
                    xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    // 创建缓存数据
                    object[,] objData = new object[RowCount + 1, ColCount];
                    //获取列标题
                    foreach (DataGridViewColumn col in myDGV.Columns)
                    {
                        objData[RowIndex, ColIndex++] = col.HeaderText;
                    }
                    // 获取数据
                    for (RowIndex = 1; RowIndex < RowCount; RowIndex++)
                    {
                        for (ColIndex = 0; ColIndex < ColCount; ColIndex++)
                        {
                            if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string)
                              || myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" ";
                            {
                                objData[RowIndex, ColIndex] = "" + myDGV[ColIndex, RowIndex - 1].Value;
                            }
                            else
                            {
                                objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex - 1].Value;
                            }
                        }
                        System.Windows.Forms.Application.DoEvents();
                    }
                    // 写入Excel
                    range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount, ColCount]);
                    range.Value2 = objData;

                    xlBook.Saved = true;
                    xlBook.SaveCopyAs(saveFileDialog.FileName);
                }
                catch (Exception err)
                {
                    Console.WriteLine("错误时间：" + DateTime.Now + "\r\n" + err);
                    result = 9999;
                }
                finally
                {
                    xlApp.Quit();
                    GC.Collect(); //强制回收
                }
                //返回值
                result = 0;
            }

            return result;
        }

        private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
            Font font = new Font("宋体", 12);//打印的字体
            e.Graphics.DrawString("sec-scan安全团队", font, Brushes.Red, 400, 20);//打印的标题
            e.Graphics.DrawString("创建时间" + DateTime.Now.ToLongDateString(), font, Brushes.Black, 20, 40);//打印的时间
            int x = 10;
            int y = 70;
            PrintDataGridView.Print(dataGridView1, true, e, ref x, ref y);    //注意：PrintDataGridView该类位于上面所写的业务逻辑层之中
        }

        private void 打印ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Console.WriteLine("正在打印数据，请稍后.......");
            MessageBox.Show("打印数据的情况下将自动调用可打印的设备");
            this.printPreviewDialog1.ShowDialog();//设置打印文档
        }
    }
}
